package org.streets.workflow.engine.persistence;


import java.sql.Date;
import java.util.List;

import org.slf4j.Logger;
import org.streets.commons.util.GUID;
import org.streets.commons.util.StringUtils;
import org.streets.database.ExecMode;
import org.streets.database.Limits;
import org.streets.database.PV;
import org.streets.database.SQLConnection;
import org.streets.workflow.engine.IProcessDefinition;
import org.streets.workflow.engine.IProcessInstance;
import org.streets.workflow.engine.IProcessTrace;
import org.streets.workflow.engine.ITaskInstance;
import org.streets.workflow.engine.IToken;
import org.streets.workflow.engine.IWorkItem;
import org.streets.workflow.engine.entities.ProcessDefinition;
import org.streets.workflow.engine.entities.ProcessInstance;
import org.streets.workflow.engine.entities.ProcessTrace;
import org.streets.workflow.engine.entities.TaskInstance;
import org.streets.workflow.engine.entities.Token;
import org.streets.workflow.engine.entities.WorkItem;

public class WorkflowDAO implements IWorkflowDAO {

	private final Logger logger;
	private final SQLConnection connection;
	
	public WorkflowDAO(Logger logger, SQLConnection connection) {
		this.logger = logger;
		this.connection = connection;
	}
	
	public void abortProcess(IProcessInstance process) {
		
		Date now = new Date(System.currentTimeMillis());
		
		((ProcessInstance)process).setState(IProcessInstance.CANCELED);
		((ProcessInstance)process).setEndTime(now);		
		connection.execute(process, ExecMode.UPDATE);
		
		//更新所有的任务实例状态为canceled		
		String sql = "update WF_RT_TASK as m set m.state = :state, m.end_time = :endTime, m.can_be_withdrawn = :canBewithdrawn where m.process_id = :processId and (m.state=0 or m.state=1)";
		connection.execute(sql, 
				PV.nv("state", ITaskInstance.CANCELED),
				PV.nv("endTime", now),
				PV.nv("canBewithdrawn", Boolean.FALSE),
				PV.nv("processId", process.getId()));
		//更新所有工作项的状态为canceled
		sql = "update WF_RT_WORKITEM as m set m.state = :state, m.end_time = :endTime where m.task_id in (select id from WF_RT_TASK n where n.process_id = :processId) and (m.state=0 or m.state=1)";
		connection.execute(sql, 
				PV.nv("state", IWorkItem.CANCELED),
				PV.nv("endTime", now),
				PV.nv("processId", process.getId()));
		//删除所有的token
		sql = "delete WF_RT_TOKEN where process_Id=:processId";
		connection.execute(sql, PV.of(process.getId()));
		
		logger.info("ProcessInstance ["+process.getId()+"] aborted.");
		
	}

	public void abortTask(ITaskInstance task) {
		Date now = new Date(System.currentTimeMillis());
        //首先Cancel TaskInstance
        ((TaskInstance)task).setState(ITaskInstance.CANCELED);
        ((TaskInstance)task).setEndTime(now);
        ((TaskInstance)task).setCanBeWithdrawn(Boolean.FALSE);        
        connection.execute((TaskInstance)task, ExecMode.UPDATE);
        //Cancel 工作项
        String sql = "update WF_RT_WORKITEM m set m.state=:state, m.end_time=:endTime where m.task_id=:taskId and (m.state=0 or m.state=1)";
        connection.execute(sql, 
        		PV.nv("state", IWorkItem.CANCELED),
        		PV.nv("endTime", now),
        		PV.nv("task", task.getId()));
        
        logger.info("TaskInstance ["+task.getId()+"] aborted.");        
	}

	public void deleteToken(IToken token) {
		connection.execute((Token)token, ExecMode.DELETE);		
	}

	public Integer countAliveSubProcess(String taskId) {
		
		String sql = "select * from WF_RT_PROCESS where parent_task_id = ? and (process_state = ? or process_state = ?)";
		
		long l = connection.count(sql, PV.of(taskId, IProcessInstance.INITIALIZED, IProcessInstance.RUNNING));
		
        return new Integer((int)l);
	}

	public Integer countAliveTaskForActivityModel(String processId, String activityModelId) {
		
		String sql = "select * from WF_RT_TASK where process_id = ? and activity_model_id = ? and (task_state = ? or task_state = ?)";

		long l = connection.count(sql, PV.of(processId, activityModelId, ITaskInstance.INITIALIZED, ITaskInstance.RUNNING ));
		
		return new Integer((int)l);
	}

	public Integer countCompletedTaskForTaskModel(String processId, String taskModelId) {
		
		String sql = "select * from WF_RT_TASK where process_id = ? and task_model_id = ? and task_state = ?";

		long l = connection.count(sql, PV.of(processId, taskModelId, ITaskInstance.COMPLETED));
		
		return new Integer((int)l);
	}


	public Integer countAliveTokenForNode(String processId, String nodeId) {
		
		String sql = "select * from WF_RT_TOKEN where process_id = ? and node_id = ? and token_state = ?";
		
		long l = connection.count(sql, PV.of(processId, nodeId, Boolean.TRUE));
		
		return new Integer((int)l);
	}

	public Integer countAliveWorkItem(String taskId) {
				
        String sql = "select * from WF_RT_WORKITEM where task_id= ? and (item_state=? or item_state=?)";
        
        long l = connection.count(sql, PV.of(taskId, IWorkItem.INITIALIZED, IWorkItem.RUNNING));
        
        return new Integer((int)l);
	}
	
	public Integer countHaveDoneWorkItems(String actorId, String publishUser)
			throws RuntimeException {
	    
      String sql = "select * from WF_RT_WORKITEM where actor_id=:actor and (item_state=:state1 or item_state=:state2)";
      
      if (publishUser != null) {
          sql += " and task_id in"
              +" (select t.id from WF_RT_TASK t, WF_DT_DEFINITION d where t.definition_id = d.definition_id and d.publish_user =:publisher and (t.task_state=:state3 or t.task_state=:state4))";
      }
        
      long l = connection.count(sql, 
          PV.nv("actor", actorId),
          PV.nv("state1", IWorkItem.COMPLETED),
          PV.nv("state1", IWorkItem.CANCELED),
          PV.nv("publisher", publishUser),
          PV.nv("state3", ITaskInstance.COMPLETED),
          PV.nv("state4", ITaskInstance.CANCELED));
      
      return new Integer((int)l);
	}

	public Integer countProcessByCreator(String creatorId)
			throws RuntimeException {
        
        String sql = "select id from WF_RT_PROCESS where creatorId=?";
        long l = connection.count(sql, PV.of(creatorId));
        return new Integer((int)l);
	}

	public Integer countProcessByPublisher(String publisher)
			throws RuntimeException {
	    
		String sql = "select p.id from WF_RT_PROCESS p where p.id in (select d.id from WF_DT_DEFINITION where d.publish_user=?";
		long l = connection.count(sql, PV.of(publisher));
		return new Integer((int)l);
	}

	public Integer countTodoWorkItems(String actorId, String publishUser)
			throws RuntimeException {

        String sql = "select wi.id from WF_RT_WORKITEM wi where (wi.item_state=:state1 or wi.item_state=:state2) and actor_id=:actor ";
        
        if (publishUser != null) {
            sql += " and task_id in"
                +" (select t.id from WF_RT_TASK t, WF_DT_DEFINITION d where t.definition_id = d.definition_id and d.publish_user =:publisher and (t.task_state=:state3 or t.task_state=:state4))";
        }

        long l = connection.count(sql, 
            PV.nv("actor", actorId),
            PV.nv("state1", IWorkItem.INITIALIZED),
            PV.nv("state1", IWorkItem.RUNNING),
            PV.nv("publisher", publishUser),
            PV.nv("state3", ITaskInstance.INITIALIZED),
            PV.nv("state4", ITaskInstance.RUNNING));
        
        return new Integer((int)l);
	}

	public void deleteInitializedWorkItems(String taskId) {
	    String sql = "delete from WF_RT_WORKITEM where task_id=? and state=?";
	    connection.execute(sql, PV.of(taskId, IWorkItem.INITIALIZED));		
	}

	public void deleteTokens(String processId, String nodeId) {
        String sql = "delete from WF_RT_TOKEN where process_id=? and nodeId=?";
        connection.execute(sql, PV.of(processId, nodeId));		
	}

	public void deleteTokens(String processId, List<String> nodeIdsList) {
		
        String sql = "delete from WF_RT_TOKEN where process_id=:processId and nodeId in (:nodeId)";
        connection.execute(sql, 
        		PV.nv("processId", processId),
        		PV.nv("nodeId", nodeIdsList));		
	}

	public ITaskInstance findAliveTask(String taskId) {
	    
        String sql = "select * from WF_RT_TASK where id = ? and (task_state=? or task_state=?)";
        TaskInstance task = connection.unique(sql, TaskInstance.class, PV.of(taskId, ITaskInstance.INITIALIZED, ITaskInstance.RUNNING));
        return task;
	}

	public List<IProcessDefinition> findAllDefinitions() {
		// TODO Auto-generated method stub
		return null;
	}

	@SuppressWarnings("unchecked")
    public List<IWorkItem> findCompletedWorkItems(String taskId) {
        String sql = "select * from WF_RT_WORKITEM m Where m.task_id=? And m.item_state=?";
        List items = connection.query(sql, WorkItem.class, Limits.none(), PV.of(taskId, IWorkItem.COMPLETED));
        return items;
	}

	public IProcessDefinition findDefinition(String definitionId, int version) {
	    
	    String sql = "select * from WF_DT_DEFINITION where definition_id =? and definition_version=?";	    
	    IProcessDefinition definition = connection.unique(sql, ProcessDefinition.class, PV.of(definitionId, version));
	    return definition;
	}

	public List<ProcessDefinition> findDefinitions(String definitionId) {

	    String sql = "select * from WF_DT_DEFINITION where definition_id = ?";
	    return connection.query(sql, ProcessDefinition.class, Limits.none(), PV.of(definitionId)); 
	}
	
	@SuppressWarnings("unchecked")
    public List<IWorkItem> findHaveDoneWorkItems(String actorId) {

	    String sql = "select * from WF_RT_WORKITEM where (item_state = ? or item_state= ?) and actor_id = ? ";
	    List items = connection.query(sql, WorkItem.class, Limits.none(), PV.of(IWorkItem.COMPLETED, IWorkItem.CANCELED, actorId));
	    return (List<IWorkItem>)items;
	}

	@SuppressWarnings("unchecked")
    public List<IWorkItem> findHaveDoneWorkItems(String actorId,	String processId) {
	    
	    String sql = "select a.* from WF_RT_WORKITEM a, WF_RT_TASK b where (a.item_state = ? or a.item_state= ?) and a.actor_id = ? "+
	        "and a.task_id = b.id and b.process_id = ?";
	    List items = connection.query(sql, WorkItem.class, Limits.none(), PV.of(IWorkItem.COMPLETED, IWorkItem.CANCELED, actorId, processId));
        return (List<IWorkItem>)items;
	}

	@SuppressWarnings("unchecked")
    public List<IWorkItem> findHaveDoneWorkItems(String actorId, String definitionId, String taskModelId) {

        String sql = "select a.* from WF_RT_WORKITEM a, WF_RT_TASK b where (a.item_state = ? or a.item_state= ?) and a.actor_id = ? "+
            "and a.task_id = b.id and b.definition_id = ? and b.task_model_id = ? order by create_time";
        List items = connection.query(sql, WorkItem.class, Limits.none(), PV.of(IWorkItem.COMPLETED, IWorkItem.CANCELED, actorId, taskModelId, definitionId));        
        return (List<IWorkItem>)items;
	}


	public IProcessInstance findProcess(String processId, boolean alive) {

	    String sql = "select * from WF_RT_PROCESS where id = ? and process_state = ? and process_state = ?";
	    ProcessInstance process;
	    if (alive) {
	        process = connection.unique(sql, ProcessInstance.class, PV.of(processId, IProcessInstance.INITIALIZED, IProcessInstance.RUNNING));
	    } else {
	        process = connection.unique(sql, ProcessInstance.class, PV.of(processId, IProcessInstance.COMPLETED, IProcessInstance.CANCELED));
	    }

	    return process;
	}

	@SuppressWarnings("unchecked")
    public List<ProcessTrace> findProcessTraces(String processId) {
        String sql = "select * from WF_RT_PROCESS_TRACE where process_id=? order by step_num, minor_num";
        List traces = connection.query(sql, ProcessTrace.class, Limits.none(), PV.of(processId));
		return  (List<ProcessTrace>) traces;
	}

	@SuppressWarnings("unchecked")
    public List<IProcessInstance> findProcesses(String definitionId) {
	    String sql = "select * from WF_RT_PROCESS where definition_id = ? order by create_time";
	    List processList = connection.query(sql, ProcessInstance.class, Limits.none(), PV.of(definitionId));	    
		return (List<IProcessInstance>)processList;
	}

	@SuppressWarnings("unchecked")
    public List<IProcessInstance> findProcesses(String definitionId, Integer version) {
	    String sql = "select * from WF_RT_PROCESS where definition_id = ? and definition_version = ? order by create_time";
        List processList = connection.query(sql, ProcessInstance.class, Limits.none(), PV.of(definitionId, version));        
        return (List<IProcessInstance>)processList;
	}

	public List<IProcessInstance> findProcessesByCreator(String creatorId, String publishUser, int pageSize, int pageNumber)
			throws RuntimeException {
		// TODO Auto-generated method stub
		return null;
	}

	public List<IProcessInstance> findProcessesByPublishUser(String publishUser,
			int pageSize, int pageNumber) throws RuntimeException {
		// TODO Auto-generated method stub
		return null;
	}

	public ITaskInstance findTask(String taskId) {
	    return connection.unique(TaskInstance.class, taskId);
	}

	@SuppressWarnings("unchecked")
    public List<ITaskInstance> findTasks(String processId, String activityModelId) {
	    String sql = "select * from WF_RT_TASK where process_id = ? and activity_model_id = ? order by create_time";
	    List taskList = connection.query(sql, TaskInstance.class, Limits.none(), PV.of(processId, activityModelId));
        return (List<ITaskInstance>) taskList;
	}

	@SuppressWarnings("unchecked")
    public List<ITaskInstance> findTasks(String processId, Integer step) {
	    
	    String sql = "select * from WF_RT_TASK where process_id = ? and step_num = ? order by create_time";
	    List taskList = connection.query(sql, TaskInstance.class, Limits.none(), PV.of(processId, step));
        return (List<ITaskInstance>) taskList;	
    }

	@SuppressWarnings("unchecked")
    public List<IWorkItem> findTodoWorkItems(String actorId) {
	    
	    String sql = "select * from WF_RT_WORKITEM where item_state = ? or item_state = ? and actor_id = ? order by create_time";
	    
	    List itemList = connection.query(sql, WorkItem.class, Limits.none(), 
	        PV.of(IWorkItem.INITIALIZED, IWorkItem.RUNNING, actorId));
	    
	    return (List<IWorkItem>) itemList;	    
	}

	@SuppressWarnings("unchecked")
    public List<IWorkItem> findTodoWorkItems(String actorId, String processId) {

	    String sql = "select a.* from WF_RT_WORKITEM a, WF_RT_TASK where a.item_state = :state1 or a.item_state = :state2 and a.actor_id = :actor " +
	            "and a.task_id = b.id and b.process_id = :process " +
	    		"order by a.create_time";
        
        List itemList = connection.query(sql, WorkItem.class, Limits.none(), 
            PV.nv("state1", IWorkItem.INITIALIZED),
            PV.nv("state2", IWorkItem.RUNNING),
            PV.nv("actor", actorId),
            PV.nv("process", processId));
        
        return (List<IWorkItem>) itemList;      
	}

	@SuppressWarnings("unchecked")
    public List<IWorkItem> findTodoWorkItems(String actorId, String definitionId, String taskModelId) {
	    
        String sql = "select a.* from WF_RT_WORKITEM a, WF_RT_TASK b where a.item_state = :state1 or a.item_state = :state2 and a.actor_id = :actor " +
        "and a.task_id = b.id and b.definition_id = :definition and b.task_model_id = :taskModel " +
        "order by a.create_time";
	 
        List itemList = connection.query(sql, WorkItem.class, Limits.none(), 
            PV.nv("state1", IWorkItem.INITIALIZED),
            PV.nv("state2", IWorkItem.CANCELED),
            PV.nv("definition", definitionId),
            PV.nv("taskModel", taskModelId));
        
        return (List<IWorkItem>) itemList;
	}

	public List<IWorkItem> findTodoWorkItems(String actorId,
			String publishUser, int pageSize, int pageNumber)
			throws RuntimeException {
		// TODO Auto-generated method stub
		return null;
	}

	public IToken findToken(String tokenId) {
	    
	    return connection.unique(Token.class, tokenId);
	}

	@SuppressWarnings("unchecked")
    public List<IToken> findTokens(String processId, String nodeModelId) {
	    
	    String sql = "select * from WF_RT_TOKEN where process_id = :process"; 
	    if (StringUtils.isNotEmpty(nodeModelId)) {
	        sql += " and node_model_id = :nodeModel";   
	    }
	    
	    List tokenList = connection.query(sql, Token.class, Limits.none(), 
	        PV.nv("process", processId),
	        PV.nv("nodeModel", nodeModelId));
	    
        return (List<IToken>) tokenList;
	}

	public IWorkItem findWorkItem(String itemId) {	  
	    return connection.unique(WorkItem.class, itemId);
	}

	@SuppressWarnings("unchecked")
    public List<IWorkItem> findWorkItems(String taskId) {
	    
	    String sql = "select * from WF_RT_WORKITEM where task_id = ?";
	    List itemList = connection.query(sql, WorkItem.class, Limits.none(), PV.of(taskId));
        
        return (List<IWorkItem>) itemList;
	}

	public void lockTask(String taskId) {
		String sql = "select * from WF_RT_TASK where id = ? for update";
		connection.query(sql, Limits.none(), PV.of(taskId));		
	}

	public void suspendProcess(IProcessInstance process) {
        ((ProcessInstance)process).setSuspended(Boolean.TRUE);
        connection.execute(process, ExecMode.UPDATE);

        String sql = "update WF_RT_TASK m set m.suspended=:suspended where m.process_id=:process and (m.task_state=:state1 Or m.task_state=:state2)";
        connection.execute(sql, 
            PV.nv("suspended", Boolean.TRUE),
            PV.nv("process", process.getId()),
            PV.nv("state1", ITaskInstance.INITIALIZED),
            PV.nv("state2", ITaskInstance.RUNNING)
            );
    }

	public void restoreProcess(IProcessInstance process) {
	    ((ProcessInstance)process).setSuspended(Boolean.FALSE);
        connection.execute(((ProcessInstance)process), ExecMode.UPDATE);

        String sql = "update WF_RT_TASK m set m.suspended=:suspended where m.process_id=:process and (m.task_state=:state1 or m.task_state=:state2)";
        connection.execute(sql, 
            PV.nv("suspended", Boolean.FALSE),
            PV.nv("process", process.getId()),
            PV.nv("state1", ITaskInstance.INITIALIZED),
            PV.nv("state2", ITaskInstance.RUNNING));		
	}

	public void saveOrUpdateDefinition(ProcessDefinition def) {
	    //
        if (StringUtils.isEmpty(def.getId())) {
            
            String sql = "select max(definition_version) from WF_DT_DEFINITION where definition_id = ?";
            Integer latestVersion = connection.column(sql, PV.of(def.getDefinitionId()));

            if (latestVersion != null) {
                def.setDefinitionVersion(new Integer(latestVersion.intValue() + 1));
            } else {
                def.setDefinitionVersion(new Integer(1));
            }            
            def.setId(GUID.nextUUID());
            connection.execute(def, ExecMode.CREATE);
            
        } else {            
            connection.execute(def, ExecMode.UPDATE);
        }
	}

	public void saveOrUpdateProcess(IProcessInstance process) {
	    
	    if (StringUtils.isEmpty(process.getId())) {	        
	        ((ProcessInstance)process).setId(GUID.nextUUID());	        
	        connection.execute((ProcessInstance)process, ExecMode.CREATE);	        
	    } else {	        
	        connection.execute((ProcessInstance)process, ExecMode.UPDATE);
	    }		
	}

	public void saveOrUpdateProcessTrace(IProcessTrace trace) {
	    
        if (StringUtils.isEmpty(trace.getId())) {            
            ((ProcessTrace)trace).setId(GUID.nextUUID());            
            connection.execute((ProcessTrace)trace, ExecMode.CREATE);
        } else {
            connection.execute((ProcessTrace)trace, ExecMode.UPDATE);
        }		
	}

	public void saveOrUpdateTask(ITaskInstance task) {
	    
        if (StringUtils.isEmpty(task.getId())) {            
            ((TaskInstance)task).setId(GUID.nextUUID());
            connection.execute((TaskInstance)task, ExecMode.CREATE);
        } else {
            connection.execute((TaskInstance)task, ExecMode.UPDATE);
        }       		
	}

	public void saveOrUpdateToken(IToken token) {
	    
        if (StringUtils.isEmpty(token.getId())) {            
            ((Token)token).setId(GUID.nextUUID());
            connection.execute((Token)token, ExecMode.CREATE);
        } else {
            connection.execute((Token)token, ExecMode.UPDATE);
        }               
	}

	public void saveOrUpdateWorkItem(IWorkItem workitem) {

	    if (StringUtils.isEmpty(workitem.getId())) {            
            ((WorkItem)workitem).setId(GUID.nextUUID());
            connection.execute((WorkItem)workitem, ExecMode.CREATE);
        } else {
            connection.execute((WorkItem)workitem, ExecMode.UPDATE);
        }               
	}
}
